package com.geekchique.runningbuddy.provider;

import java.util.HashMap;
import java.util.Map;

import android.content.ContentProvider;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.Resources;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.util.Log;

/**
 * 
 * The following URIs are offered by this Content Provider
 * 
 * Routes:
 * <P>
 * content://com.novoda.runbuddy/routes
 * </P>
 * The following will give the DB projection and the geoLocation.
 * <P>
 * content://com.novoda.runbuddy/route/#
 * </p>
 * 
 * Runs:
 * <P>
 * </P>
 * 
 * Checkpoints:
 * <P>
 * </P>
 * 
 * GeoLocations:
 * <P>
 * </P>
 * 
 * RunSegments:
 * <P>
 * </P>
 * 
 * 
 * RouteSegments:
 * <P>
 * </P>
 */
public class RunBuddyContentProvider extends ContentProvider {

	private static final String			LOGTAG						= "MyLog";

	private SQLiteDatabase				mDB;

	private static final String			DATABASE_NAME				= "runningBuddyDB3";

	private static final int			DATABASE_VERSION			= 1;

	private static final String			URI_BASE					= "com.novoda.runbuddy";

	private static final UriMatcher		URI_MATCHER					= new UriMatcher(UriMatcher.NO_MATCH);

	private static final int			ROUTES						= 0;
	private static final int			ROUTE_ID					= 1;
	private static Map<String, String>	ROUTE_LIST_PROJECTION_MAP	= null;
	private static final String			ROUTE_LOCATION_JOIN			= "route LEFT OUTER JOIN geoLocation ON (route._idGeolocation = geoLocation._id)";

	private static class DatabaseHelper extends SQLiteOpenHelper {
		private static final String	CREATE_TABLE_ACCOUNT		= "CREATE TABLE account (__id NUMERIC, _id INTEGER PRIMARY KEY, createdAt DATETIME, isDefault BOOLEAN, modifiedAt DATETIME, password TEXT, userName TEXT);";
		private static final String	CREATE_TABLE_CHECKPOINT		= "CREATE TABLE checkPoint (modifiedAt DATETIME, createdAt DATETIME, _idGeoLocation NUMERIC, __id NUMERIC, _id INTEGER PRIMARY KEY, desc TEXT, implementor TEXT, name TEXT);";
		private static final String	CREATE_TABLE_GEO_LOCATION	= "CREATE TABLE geoLocation (modfiedAt DATETIME, createdAt DATETIME, _id INTEGER PRIMARY KEY, address1 TEXT, address2 TEXT, altitude NUMERIC, countryCode TEXT, latitude NUMERIC, longitude NUMERIC, name TEXT, postcode TEXT);";
		private static final String	CREATE_TABLE_ROUTE			= "CREATE TABLE route (_idGeoLocation NUMERIC, modifiedAt DATETIME, createdAt DATETIME, name TEXT, __id NUMERIC, _id INTEGER PRIMARY KEY);";
		private static final String	CREATE_TABLE_ROUTE_POINT	= "CREATE TABLE routePoint (_id INTEGER PRIMARY KEY, _idGeoLocation NUMERIC, _idRoute NUMERIC, algotrithm NUMERIC, createdAt DATETIME, direction NUMERIC, modifiedAt DATETIME, pointNo NUMERIC);";
		private static final String	CREATE_TABLE_RUN			= "CREATE TABLE run (modifiedAt NUMERIC, createdAt NUMERIC, totalTime NUMERIC, _idRoute NUMERIC, _id INTEGER PRIMARY KEY, name TEXT);";
		private static final String	CREATE_TABLE_ROUTE_SEGMENT	= "CREATE TABLE routeSegment (_idCheckPoint NUMERIC, _id INTEGER PRIMARY KEY, _idRoute NUMERIC, checkPointNo NUMERIC, distance NUMERIC);";
		private static final String	CREATE_TABLE_RUN_SEGMENT	= "CREATE TABLE runSegment (checkPointNo NUMERIC, modifedAt DATETIME, createdAt DATETIME, _idCheckPoint NUMERIC, _idRun NUMERIC, _id INTEGER PRIMARY KEY, lap NUMERIC, time NUMERIC);";
		private static final String	CREATE_TABLE_VERSION		= "CREATE TABLE version (_id INTEGER PRIMARY KEY, _idAccount NUMERIC, isInSynch BOOLEAN, lastSynch DATETIME, versionNo NUMERIC);";

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_TABLE_ACCOUNT);
			db.execSQL(CREATE_TABLE_CHECKPOINT);
			db.execSQL(CREATE_TABLE_GEO_LOCATION);
			db.execSQL(CREATE_TABLE_ROUTE_POINT);
			db.execSQL(CREATE_TABLE_ROUTE);
			db.execSQL(CREATE_TABLE_ROUTE_SEGMENT);
			db.execSQL(CREATE_TABLE_RUN);
			db.execSQL(CREATE_TABLE_RUN_SEGMENT);
			db.execSQL(CREATE_TABLE_VERSION);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(LOGTAG, "Upgrading database from version " + oldVersion + " to " + newVersion
					+ ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS account");
			db.execSQL("DROP TABLE IF EXISTS checkPoint");
			db.execSQL("DROP TABLE IF EXISTS geoLocation");
			db.execSQL("DROP TABLE IF EXISTS routePoint");
			db.execSQL("DROP TABLE IF EXISTS run");
			db.execSQL("DROP TABLE IF EXISTS runSegment");
			db.execSQL("DROP TABLE IF EXISTS version");
			onCreate(db);
		}
	}

	@Override
	public boolean onCreate() {
		DatabaseHelper dbHelper = new DatabaseHelper();
		mDB = dbHelper.openDatabase(getContext(), DATABASE_NAME, null, DATABASE_VERSION);
		return (mDB == null) ? false : true;
	}

	public int delete(Uri uri, String selection, String[] selectionArgs) {
		return 0;
	}

	public String getType(Uri uri) {
		int match = URI_MATCHER.match(uri);
		switch (match) {
			case ROUTES:
				return "vnd.android.cursor.dir/vnd.runbuddy.route";
			case ROUTE_ID:
				return "vnd.android.cursor.item/vnd.runbuddy.route";
			default:
				throw new IllegalArgumentException("Unknown URL " + uri);
		}
	}

	public Uri insert(Uri uri, ContentValues values) {
		int match = URI_MATCHER.match(uri);
		switch (match) {
			case ROUTES:
				return insertRoute(values);
			default:
				throw new IllegalArgumentException("Unknown URL " + uri);
		}

	}

	private Uri insertRoute(ContentValues initialValues) {
		long rowID;
		ContentValues values;
		if (initialValues != null) {
			values = new ContentValues(initialValues);
		} else {
			values = new ContentValues();
		}
		Long now = Long.valueOf(System.currentTimeMillis());
		Resources r = Resources.getSystem();

		if (values.containsKey(RunBuddyCP.Routes.NAME) == false) {
			values.put(RunBuddyCP.Routes.NAME, r.getString(android.R.string.untitled));
		}

		// TODO will need to think about this...
		if (values.containsKey(RunBuddyCP.Routes.SERVER_ID) == false) {
			values.put(RunBuddyCP.Routes.SERVER_ID, 1);
		}

		if (values.containsKey(RunBuddyCP.Routes.CREATED_DATE) == false) {
			values.put(RunBuddyCP.Routes.CREATED_DATE, now);
		}

		if (values.containsKey(RunBuddyCP.Routes.MODIFIED_DATE) == false) {
			values.put(RunBuddyCP.Routes.MODIFIED_DATE, now);
		}

		mDB.compileStatement("BEGIN TRANSACTION");
		rowID = mDB.insert("route", "name", values);
		if (rowID > 0) {

			ContentValues geoValues = new ContentValues();

			// Latitude/longitude part computed from the list of routePoints.
			if (values.containsKey(RunBuddyCP.Routes.LATITUDE) == false || values.containsKey(RunBuddyCP.Routes.LONGITUDE) == false) {
				long[] center = computeCenterOfRoute(rowID);
				geoValues.put(RunBuddyCP.Routes.LATITUDE, center[0]);
				geoValues.put(RunBuddyCP.Routes.LONGITUDE, center[1]);
			} else {
				geoValues.put(RunBuddyCP.Routes.LATITUDE, values.getAsLong(RunBuddyCP.Routes.LATITUDE));
				geoValues.put(RunBuddyCP.Routes.LONGITUDE, values.getAsLong(RunBuddyCP.Routes.LONGITUDE));
			}

			values.remove(RunBuddyCP.Routes.LATITUDE);
			values.remove(RunBuddyCP.Routes.LONGITUDE);

			long geoLocationRowID = mDB.insert("geoLocation", "name", geoValues);
			values.put("_idGeolocation", geoLocationRowID);

			mDB.update("route", values, "_id=" + rowID, null);
			Uri uri = ContentUris.appendId(RunBuddyCP.Routes.CONTENT_URI.buildUpon(), rowID).build();

			mDB.compileStatement("END TRANSACTION");

			getContext().getContentResolver().notifyChange(uri, null);
			return uri;
		}
		throw new SQLException("Failed to insert row");
	}

	/**
	 * This method uses the SQLite database to compute the minimum and maximum
	 * values from a list of geoLocation knowing the route id.
	 * 
	 * @param routeId
	 *            the route id against which we will compute the center of the
	 *            map
	 * @return an array of 2 ints, the first is the latitude center and the
	 *         second the longitude. -1 if it can not be computed.
	 */
	protected long[] computeCenterOfRoute(long routeId) {
		long[] ret = new long[] { -1l, -1l };

		final int MAX_LATITUDE_CURSOR_INDEX = 0;
		final int MIN_LATITUDE_CURSOR_INDEX = 1;

		final int MAX_LONGITUDE_CURSOR_INDEX = 2;
		final int MIN_LONGITUDE_CURSOR_INDEX = 3;

		Cursor c = null;
		// using SQL to get the min and max for longitude and latitude. from
		// there get the center
		String sql = "SELECT max(latitude), min(latitude), max(longitude), min(longitude) "
				+ "from geoLocation LEFT OUTER JOIN routePoint ON (routePoint._idGeolocation = geoLocation._id) "
				+ "WHERE routePoint._idRoute=?;";
		c = mDB.rawQuery(sql, new String[] { ("" + routeId) });

		if (c.first()) {
			ret[0] = (long) c.getLong(MIN_LATITUDE_CURSOR_INDEX)
					+ (c.getLong(MAX_LATITUDE_CURSOR_INDEX) - c.getLong(MIN_LATITUDE_CURSOR_INDEX)) / 2;
			ret[1] = (long) c.getLong(MIN_LONGITUDE_CURSOR_INDEX)
					+ (c.getLong(MAX_LONGITUDE_CURSOR_INDEX) - c.getLong(MIN_LONGITUDE_CURSOR_INDEX)) / 2;
		}
		return ret;
	}

	public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {

		SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
		Cursor c = null;

		int match = URI_MATCHER.match(uri);
		switch (match) {
			case ROUTES:
				qb.setTables(ROUTE_LOCATION_JOIN);
				qb.setProjectionMap(ROUTE_LIST_PROJECTION_MAP);
				break;
			case ROUTE_ID:
				qb.setTables(ROUTE_LOCATION_JOIN);
				qb.setProjectionMap(ROUTE_LIST_PROJECTION_MAP);
				qb.appendWhere("route._id=" + uri.getPathSegments().get(1));
				break;
			default:
				throw new IllegalArgumentException("Unknown URL " + uri);
		}

		c = qb.query(mDB, projection, selection, selectionArgs, null, null, sortOrder);
		c.setNotificationUri(getContext().getContentResolver(), uri);
		return c;
	}

	public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {
		return 0;
	}

	static {
		URI_MATCHER.addURI(URI_BASE, "routes", ROUTES);
		URI_MATCHER.addURI(URI_BASE, "routes/#", ROUTE_ID);

		ROUTE_LIST_PROJECTION_MAP = new HashMap<String, String>();
		ROUTE_LIST_PROJECTION_MAP.put(RunBuddyCP.Routes._ID, "route._id");
		ROUTE_LIST_PROJECTION_MAP.put(RunBuddyCP.Routes.NAME, "route.name");
		// GeoLocation_id is replaced with the actual values from the
		// GeoLocation (lat/long in this case)
		ROUTE_LIST_PROJECTION_MAP.put(RunBuddyCP.Routes.LATITUDE, "geoLocation.latitude");
		ROUTE_LIST_PROJECTION_MAP.put(RunBuddyCP.Routes.LONGITUDE, "geoLocation.longitude");
		ROUTE_LIST_PROJECTION_MAP.put(RunBuddyCP.Routes.SERVER_ID, "route.__id");
		ROUTE_LIST_PROJECTION_MAP.put(RunBuddyCP.Routes.CREATED_DATE, "route.createdAt");
		ROUTE_LIST_PROJECTION_MAP.put(RunBuddyCP.Routes.MODIFIED_DATE, "route.modifiedAt");
	}
}